package dao;

import classes.Ausencia;
import classes.AusenciaTipo;
import classes.Gozo;
import classes.Lotacao;
import classes.Servidor;
import conexao.GerenciadorConexao;
import erro.ConexaoException;
import erro.ConsultaException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 *
 * @author roberta.m.carvalho
 */
public class AusenciaDao {

    private GerenciadorConexao g;

    public AusenciaDao() {
        g = GerenciadorConexao.getInstancia();
    }

    public void inserirAusenciaTipo(AusenciaTipo at) throws ConexaoException, ConsultaException {
        String sql = "INSERT INTO ausenciatipo (tipoNome) VALUES (?)";
        try {
            PreparedStatement pstm = (PreparedStatement) g.connect().prepareStatement(sql);
            pstm.setString(1, at.getNome());
            pstm.executeUpdate();

        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
    }

    public void inserirGozo(Gozo gozo, Ausencia ausencia) throws ConexaoException, ConsultaException {
        String sql = "INSERT INTO gozo (dias, dataInicial, dataFinal, matricula, ausenciaId,status,aberto) VALUES (?,?,?,?,?,?,?)";
        String sqlA = "UPDATE ausencia SET saldo=? WHERE ausenciaId=?";
        try {
            Date gozoI = gozo.getDataInicial();
            Date gozoF = gozo.getDataFinal();
            g.connect().setAutoCommit(false);
            PreparedStatement pstm = (PreparedStatement) g.connect().prepareStatement(sql);
            PreparedStatement pstmA = (PreparedStatement) g.connect().prepareStatement(sqlA);
            pstm.setLong(1, gozo.getDias());
            pstm.setDate(2, new java.sql.Date(gozoI.getTime()));
            pstm.setDate(3, new java.sql.Date(gozoF.getTime()));
            pstm.setInt(4, gozo.getServidor().getMatricula());
            pstm.setInt(5, gozo.getAusencia().getCod());
            pstm.setInt(6, gozo.getStatus());
            pstm.setInt(7, gozo.getAberto());
            pstm.executeUpdate();

            pstmA.setLong(1, ausencia.getSaldo());
            pstmA.setInt(2, ausencia.getCod());
            pstmA.executeUpdate();

            g.connect().commit();
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
    }

    public void inserirAusencia(Ausencia a) throws ConexaoException, ConsultaException {
        String sql = "INSERT INTO ausencia (matricula,idausenciaTipo,aquisInicial,aquisFinal,gozoI,gozoF,saldo,obs,tipoNome,dias) "
                + "VALUES (?,?,?,?,?,?,?,?,?,?)";
        try {
            Date data1 = a.getAquisitivoInicial();
            Date data2 = a.getAquisitivoFinal();
            Date gozo = a.getGozoI();
            a.setGozoI(gozo);
            Date gozoF = a.getGozoF();
            a.setGozoF(gozoF);

            PreparedStatement pstm = (PreparedStatement) g.connect().prepareStatement(sql);
            pstm.setInt(1, a.getServidor().getMatricula());
            pstm.setInt(2, a.getTipo().getCodTipo());
            pstm.setDate(3, new java.sql.Date(data1.getTime()));
            pstm.setDate(4, new java.sql.Date(data2.getTime()));
            pstm.setDate(5, new java.sql.Date(gozo.getTime()));
            pstm.setDate(6, new java.sql.Date(gozoF.getTime()));
            pstm.setLong(7, a.getSaldo());
            pstm.setString(8, a.getObs());
            pstm.setString(9, a.getTipoNome());
            pstm.setInt(10, a.getDias());
            pstm.executeUpdate();

        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
    }

    public List<AusenciaTipo> listarTipo() throws ConexaoException, ConsultaException {
        List<AusenciaTipo> lista = new ArrayList<AusenciaTipo>();
        AusenciaTipo at;
        String sql = "SELECT * FROM ausenciatipo ORDER BY tipoNome";
        try {
            Statement stm = (Statement) g.connect().createStatement();
            ResultSet rs = stm.executeQuery(sql);
            while (rs.next()) {
                at = new AusenciaTipo();
                at.setCodTipo(rs.getInt("idausenciaTipo"));
                at.setNome(rs.getString("tipoNome"));
                lista.add(at);
            }
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
        return lista;
    }

    public void deletar(AusenciaTipo at) throws ConexaoException, ConsultaException {

        String sql = "DELETE FROM ausenciatipo where idausenciaTipo =?";
        try {
            PreparedStatement pstm = (PreparedStatement) g.connect().prepareStatement(sql);
            pstm.setInt(1, at.getCodTipo());
            pstm.executeUpdate();
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
    }

    public void deletarGozo(Gozo gozo) throws ConexaoException, ConsultaException {

        String sql = "DELETE FROM gozo WHERE idGozo =?";
        try {

            PreparedStatement pstm = (PreparedStatement) g.connect().prepareStatement(sql);
            pstm.setInt(1, gozo.getCod());
            pstm.executeUpdate();
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
    }

    public void deletarAusencia(Ausencia at, Gozo gozo) throws ConexaoException, ConsultaException {

        String sql = "DELETE FROM ausencia WHERE ausenciaId =?";
        String sqlGozo = "DELETE FROM gozo WHERE ausenciaId =?";
        try {
            g.connect().setAutoCommit(false);
            PreparedStatement pstm = (PreparedStatement) g.connect().prepareStatement(sql);
            PreparedStatement pstmGozo = (PreparedStatement) g.connect().prepareStatement(sqlGozo);

            pstmGozo.setInt(1, at.getCod());
            pstmGozo.executeUpdate();

            pstm.setInt(1, at.getCod());
            pstm.executeUpdate();

            g.connect().commit();
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
    }

    public List<Ausencia> listar() throws ConexaoException, ConsultaException {
        List<Ausencia> lista = new ArrayList<Ausencia>();
        Ausencia a;
        String sql = "SELECT a.* FROM ausencia as a "
                + "WHERE a.saldo > 0";
        try {
            Statement stm = (Statement) g.connect().createStatement();
            ResultSet rs = stm.executeQuery(sql);
            while (rs.next()) {
                a = new Ausencia();
                a.setCod(rs.getInt("a.ausenciaId"));
                a.setGozoI(rs.getDate("a.gozoI"));
                a.setGozoF(rs.getDate("a.gozoF"));
                a.setAquisitivoInicial(rs.getDate("a.aquisInicial"));
                a.setAquisitivoFinal(rs.getDate("a.aquisFinal"));
                a.setSaldo(rs.getInt("a.saldo"));
                a.setObs(rs.getString("a.obs"));
                Servidor s = new Servidor();
                s.setMatricula(rs.getInt("a.matricula"));
                a.setServidor(s);
                AusenciaTipo at = new AusenciaTipo();
                at.setCodTipo(rs.getInt("a.idausenciaTipo"));
                a.setTipo(at);
                a.setTipoNome(rs.getString("a.tipoNome"));
                lista.add(a);
            }
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
        return lista;
    }

    public List<Ausencia> listarPeriodos(Servidor matricula) throws ConexaoException, ConsultaException {
        List<Ausencia> lista = new ArrayList<Ausencia>();
        Ausencia a;
        String sql = "SELECT * FROM ausencia "
                + "WHERE matricula =? "
                + "AND saldo > 0";
        try {
            PreparedStatement pstm = g.connect().prepareStatement(sql);
            pstm.setInt(1, matricula.getMatricula());
            ResultSet rs = pstm.executeQuery();
            while (rs.next()) {
                a = new Ausencia();
                a.setCod(rs.getInt("ausenciaId"));
                a.setGozoI(rs.getDate("gozoI"));
                a.setGozoF(rs.getDate("gozoF"));
                a.setAquisitivoInicial(rs.getDate("aquisInicial"));
                a.setAquisitivoFinal(rs.getDate("aquisFinal"));
                a.setSaldo(rs.getInt("saldo"));
                a.setObs(rs.getString("obs"));
                Servidor s = new Servidor();
                s.setMatricula(rs.getInt("matricula"));
                a.setServidor(s);
                AusenciaTipo at = new AusenciaTipo();
                at.setCodTipo(rs.getInt("idausenciaTipo"));
                a.setTipo(at);
                lista.add(a);
            }
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
        return lista;
    }

    public void alterarSaldo(Ausencia a) throws ConexaoException, ConsultaException {

        String sql = "UPDATE ausencia SET saldo=? WHERE ausenciaId=?";
        try {
            PreparedStatement pstm = (PreparedStatement) g.connect().prepareStatement(sql);
            pstm.setLong(1, a.getSaldo());
            pstm.setInt(2, a.getCod());
            pstm.executeUpdate();
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
    }

    public void alterarGozo(Gozo gozo) throws ConexaoException, ConsultaException {

        String sql = "UPDATE gozo SET dias=?, status=?,dataInicial=?,"
                + "dataFinal=?,ausenciaId=? WHERE idGozo=?";
        try {
            PreparedStatement pstm = (PreparedStatement) g.connect().prepareStatement(sql);
            pstm.setLong(1, gozo.getDias());
            pstm.setInt(2, gozo.getStatus());
            pstm.setDate(3, new java.sql.Date(gozo.getDataInicial().getTime()));
            pstm.setDate(4, new java.sql.Date(gozo.getDataFinal().getTime()));
            pstm.setInt(5, gozo.getAusencia().getCod());
            pstm.setInt(6, gozo.getCod());
            pstm.executeUpdate();
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
    }

    public void alterarTipoGozo(AusenciaTipo at) throws ConexaoException, ConsultaException {

        String sql = "UPDATE ausenciatipo SET tipoNome=? WHERE idausenciaTipo=?";
        try {
            PreparedStatement pstm = (PreparedStatement) g.connect().prepareStatement(sql);
            pstm.setString(1, at.getNome());
            pstm.setInt(2, at.getCodTipo());
            pstm.executeUpdate();
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
    }

    public void alterarAusencia(Ausencia a) throws ConexaoException, ConsultaException {

        String sql = "UPDATE ausencia SET matricula=?,idausenciaTipo=?,aquisInicial=?,aquisFinal=?,gozoI=?,gozoF=?,saldo=?,obs=?,tipoNome=? "
                + "WHERE ausenciaId=?";
        try {
            Date data1 = a.getAquisitivoInicial();
            Date data2 = a.getAquisitivoFinal();
            Date gozo = a.getGozoI();
            a.setGozoI(gozo);
            Date gozoF = a.getGozoF();
            a.setGozoF(gozoF);

            PreparedStatement pstm = (PreparedStatement) g.connect().prepareStatement(sql);
            pstm.setInt(1, a.getServidor().getMatricula());
            pstm.setInt(2, a.getTipo().getCodTipo());
            pstm.setDate(3, new java.sql.Date(data1.getTime()));
            pstm.setDate(4, new java.sql.Date(data2.getTime()));
            pstm.setDate(5, new java.sql.Date(gozo.getTime()));
            pstm.setDate(6, new java.sql.Date(gozoF.getTime()));
            pstm.setLong(7, a.getSaldo());
            pstm.setString(8, a.getObs());
            pstm.setString(9, a.getTipoNome());
            pstm.setInt(10, a.getCod());
            pstm.executeUpdate();
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
    }

    public Ausencia PesquisarPeriodo(Gozo gozo) throws ConexaoException, ConsultaException {
        Ausencia a = null;
        String sql = "SELECT a.ausenciaId, a.gozoI,a.gozoF,a.aquisInicial,a.aquisFinal,a.saldo,a.obs,"
                + "a.matricula,a.idausenciaTipo,at.tipoNome "
                + "FROM ausencia a, gozo g, ausenciatipo at "
                + "WHERE g.ausenciaId = a.ausenciaId "
                + "AND at.idausenciaTipo = a.idausenciaTipo "
                + "AND g.idGozo = ?";
        try {
            PreparedStatement pstm = g.connect().prepareStatement(sql);
            pstm.setInt(1, gozo.getCod());
            ResultSet rs = pstm.executeQuery();
            while (rs.next()) {
                a = new Ausencia();
                a.setCod(rs.getInt("a.ausenciaId"));
                a.setGozoI(rs.getDate("a.gozoI"));
                a.setGozoF(rs.getDate("a.gozoF"));
                a.setAquisitivoInicial(rs.getDate("a.aquisInicial"));
                a.setAquisitivoFinal(rs.getDate("a.aquisFinal"));
                a.setSaldo(rs.getInt("a.saldo"));
                a.setObs(rs.getString("a.obs"));
                Servidor s = new Servidor();
                s.setMatricula(rs.getInt("a.matricula"));
                a.setServidor(s);
                AusenciaTipo at = new AusenciaTipo();
                at.setCodTipo(rs.getInt("a.idausenciaTipo"));
                at.setNome(rs.getString("at.tipoNome"));
                a.setTipo(at);
            }
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
        return a;
    }

    public List<Gozo> listarGozo(Lotacao l) throws ConexaoException, ConsultaException {
        List<Gozo> lista = new ArrayList<Gozo>();
        Gozo gozo;
        String sql = "SELECT g.* , a.*,s.nome,s.nomeExibido,s.matricula,s.emailProf,s.idlotacao "
                + "FROM gozo as g,ausencia as a,servidor as s "
                + "WHERE g.ausenciaId = a.ausenciaId AND g.matricula = s.matricula AND s.idlotacao = ?";
        try {
            PreparedStatement pstm = g.connect().prepareStatement(sql);
            pstm.setInt(1, l.getLotacao_id());
            ResultSet rs = pstm.executeQuery();
            while (rs.next()) {
                gozo = new Gozo();
                gozo.setCod(rs.getInt("g.idGozo"));
                gozo.setDias(rs.getInt("g.dias"));
                gozo.setDataInicial(rs.getDate("g.dataInicial"));
                gozo.setDataFinal(rs.getDate("g.dataFinal"));
                Servidor s = new Servidor();
                s.setMatricula(rs.getInt("s.matricula"));
                s.setNome(rs.getString("s.nome"));
                s.setEmailProf(rs.getString("s.emailProf"));
                s.setNomeExibicao(rs.getString("s.nomeExibido"));
                Lotacao lot = new Lotacao();
                lot.setLotacao_id(rs.getInt("s.idlotacao"));
                s.setLotacao(lot);
                gozo.setServidor(s);
                Ausencia at = new Ausencia();
                at.setCod(rs.getInt("a.ausenciaId"));
                at.setAquisitivoInicial(rs.getDate("a.aquisInicial"));
                at.setAquisitivoFinal(rs.getDate("a.aquisFinal"));
                at.setTipoNome(rs.getString("a.tipoNome"));
                at.setSaldo(rs.getInt("a.saldo"));
                gozo.setAusencia(at);
                gozo.setStatus(rs.getInt("g.status"));
                gozo.setAberto(rs.getInt("g.aberto"));
                lista.add(gozo);
            }
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
        return lista;
    }

    public List<Gozo> listarGozoAberto(Lotacao l) throws ConexaoException, ConsultaException {
        List<Gozo> lista = new ArrayList<Gozo>();
        Gozo gozo;
        String sql = "SELECT g.* , a.*,s.nome,s.nomeExibido,s.matricula,s.emailProf,s.idlotacao "
                + "FROM gozo as g,ausencia as a,servidor as s "
                + "WHERE g.aberto = 1 "
                + "AND g.ausenciaId = a.ausenciaId AND g.matricula = s.matricula AND s.idlotacao = ?";
        try {
            PreparedStatement pstm = g.connect().prepareStatement(sql);
            pstm.setInt(1, l.getLotacao_id());
            ResultSet rs = pstm.executeQuery();
            while (rs.next()) {
                gozo = new Gozo();
                gozo.setCod(rs.getInt("g.idGozo"));
                gozo.setDias(rs.getInt("g.dias"));
                gozo.setDataInicial(rs.getDate("g.dataInicial"));
                gozo.setDataFinal(rs.getDate("g.dataFinal"));
                Servidor s = new Servidor();
                s.setMatricula(rs.getInt("s.matricula"));
                s.setNome(rs.getString("s.nome"));
                s.setNomeExibicao(rs.getString("nomeExibido"));
                s.setEmailProf(rs.getString("s.emailProf"));
                Lotacao lot = new Lotacao();
                lot.setLotacao_id(rs.getInt("s.idlotacao"));
                s.setLotacao(lot);
                gozo.setServidor(s);
                Ausencia at = new Ausencia();
                at.setCod(rs.getInt("a.ausenciaId"));
                at.setAquisitivoInicial(rs.getDate("a.aquisInicial"));
                at.setAquisitivoFinal(rs.getDate("a.aquisFinal"));
                at.setTipoNome(rs.getString("a.tipoNome"));
                at.setSaldo(rs.getInt("a.saldo"));
                gozo.setAusencia(at);
                gozo.setStatus(rs.getInt("g.status"));
                gozo.setAberto(rs.getInt("g.aberto"));
                lista.add(gozo);
            }
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
        return lista;
    }

    public List<Gozo> listarGozoEncerrado(Lotacao l) throws ConexaoException, ConsultaException {
        List<Gozo> lista = new ArrayList<Gozo>();
        Gozo gozo;
        String sql = "SELECT g.* , a.*,s.nome,s.nomeExibido,s.matricula,s.emailProf,s.idlotacao "
                + "FROM gozo as g,ausencia as a,servidor as s "
                + "WHERE g.aberto = 2 "
                + "AND g.ausenciaId = a.ausenciaId AND g.matricula = s.matricula AND s.idlotacao = ?";
        try {
            PreparedStatement pstm = g.connect().prepareStatement(sql);
            pstm.setInt(1, l.getLotacao_id());
            ResultSet rs = pstm.executeQuery();
            while (rs.next()) {
                gozo = new Gozo();
                gozo.setCod(rs.getInt("g.idGozo"));
                gozo.setDias(rs.getInt("g.dias"));
                gozo.setDataInicial(rs.getDate("g.dataInicial"));
                gozo.setDataFinal(rs.getDate("g.dataFinal"));
                Servidor s = new Servidor();
                s.setMatricula(rs.getInt("s.matricula"));
                s.setNome(rs.getString("s.nome"));
                s.setEmailProf(rs.getString("s.emailProf"));
                s.setNomeExibicao(rs.getString("nomeExibido"));
                Lotacao lot = new Lotacao();
                lot.setLotacao_id(rs.getInt("s.idlotacao"));
                s.setLotacao(lot);
                gozo.setServidor(s);
                Ausencia at = new Ausencia();
                at.setCod(rs.getInt("a.ausenciaId"));
                at.setAquisitivoInicial(rs.getDate("a.aquisInicial"));
                at.setAquisitivoFinal(rs.getDate("a.aquisFinal"));
                at.setTipoNome(rs.getString("a.tipoNome"));
                at.setSaldo(rs.getInt("a.saldo"));
                gozo.setAusencia(at);
                gozo.setStatus(rs.getInt("g.status"));
                gozo.setAberto(rs.getInt("g.aberto"));
                lista.add(gozo);
            }
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
        return lista;
    }

    public List<Gozo> listarGozo() throws ConexaoException, ConsultaException {
        List<Gozo> lista = new ArrayList<Gozo>();
        Gozo gozo;
        String sql = "SELECT g.* , a.*,s.nome,s.nomeExibido,s.matricula,s.emailProf "
                + "FROM gozo as g,ausencia as a,servidor as s "
                + "WHERE g.ausenciaId = a.ausenciaId AND g.matricula = s.matricula";
        try {
            Statement stm = (Statement) g.connect().createStatement();
            ResultSet rs = stm.executeQuery(sql);
            while (rs.next()) {
                gozo = new Gozo();
                gozo.setCod(rs.getInt("g.idGozo"));
                gozo.setDias(rs.getInt("g.dias"));
                gozo.setDataInicial(rs.getDate("g.dataInicial"));
                gozo.setDataFinal(rs.getDate("g.dataFinal"));
                Servidor s = new Servidor();
                s.setMatricula(rs.getInt("s.matricula"));
                s.setNome(rs.getString("s.nome"));
                s.setNomeExibicao(rs.getString("s.nomeExibido"));
                s.setEmailProf(rs.getString("s.emailProf"));
                gozo.setServidor(s);
                Ausencia at = new Ausencia();
                at.setCod(rs.getInt("a.ausenciaId"));
                at.setAquisitivoInicial(rs.getDate("a.aquisInicial"));
                at.setAquisitivoFinal(rs.getDate("a.aquisFinal"));
                at.setTipoNome(rs.getString("a.tipoNome"));
                at.setSaldo(rs.getInt("a.saldo"));
                gozo.setAusencia(at);
                gozo.setStatus(rs.getInt("g.status"));
                gozo.setAberto(rs.getInt("g.aberto"));
                lista.add(gozo);
            }
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
        return lista;
    }

    public List<Gozo> listarGozoAberto() throws ConexaoException, ConsultaException {
        List<Gozo> lista = new ArrayList<Gozo>();
        Gozo gozo;
        String sql = "SELECT g.* , a.*,s.nome,s.nomeExibido,s.matricula,s.emailProf "
                + "FROM gozo as g,ausencia as a,servidor as s "
                + "WHERE g.aberto = 1 "
                + "AND g.ausenciaId = a.ausenciaId AND g.matricula = s.matricula";
        try {
            Statement stm = (Statement) g.connect().createStatement();
            ResultSet rs = stm.executeQuery(sql);
            while (rs.next()) {
                gozo = new Gozo();
                gozo.setCod(rs.getInt("g.idGozo"));
                gozo.setDias(rs.getInt("g.dias"));
                gozo.setDataInicial(rs.getDate("g.dataInicial"));
                gozo.setDataFinal(rs.getDate("g.dataFinal"));
                Servidor s = new Servidor();
                s.setMatricula(rs.getInt("s.matricula"));
                s.setNome(rs.getString("s.nome"));
                s.setEmailProf(rs.getString("s.emailProf"));
                s.setNomeExibicao(rs.getString("s.nomeExibido"));
                gozo.setServidor(s);
                Ausencia at = new Ausencia();
                at.setCod(rs.getInt("a.ausenciaId"));
                at.setAquisitivoInicial(rs.getDate("a.aquisInicial"));
                at.setAquisitivoFinal(rs.getDate("a.aquisFinal"));
                at.setTipoNome(rs.getString("a.tipoNome"));
                at.setSaldo(rs.getInt("a.saldo"));
                gozo.setAusencia(at);
                gozo.setStatus(rs.getInt("g.status"));
                gozo.setAberto(rs.getInt("g.aberto"));
                lista.add(gozo);
            }
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
        return lista;
    }

    public List<Gozo> listarGozoEncerrados() throws ConexaoException, ConsultaException {
        List<Gozo> lista = new ArrayList<Gozo>();
        Gozo gozo;
        String sql = "SELECT g.* , a.*,s.nome,s.nomeExibido,s.matricula,s.emailProf "
                + "FROM gozo as g,ausencia as a,servidor as s "
                + "WHERE g.aberto = 2 "
                + "AND g.ausenciaId = a.ausenciaId "
                + "AND g.matricula = s.matricula";
        try {
            Statement stm = (Statement) g.connect().createStatement();
            ResultSet rs = stm.executeQuery(sql);
            while (rs.next()) {
                gozo = new Gozo();
                gozo.setCod(rs.getInt("g.idGozo"));
                gozo.setDias(rs.getInt("g.dias"));
                gozo.setDataInicial(rs.getDate("g.dataInicial"));
                gozo.setDataFinal(rs.getDate("g.dataFinal"));
                Servidor s = new Servidor();
                s.setMatricula(rs.getInt("s.matricula"));
                s.setNome(rs.getString("s.nome"));
                s.setEmailProf(rs.getString("s.emailProf"));
                s.setNomeExibicao(rs.getString("s.nomeExibido"));
                gozo.setServidor(s);
                Ausencia at = new Ausencia();
                at.setCod(rs.getInt("a.ausenciaId"));
                at.setAquisitivoInicial(rs.getDate("a.aquisInicial"));
                at.setAquisitivoFinal(rs.getDate("a.aquisFinal"));
                at.setTipoNome(rs.getString("a.tipoNome"));
                at.setSaldo(rs.getInt("a.saldo"));
                gozo.setAusencia(at);
                gozo.setStatus(rs.getInt("g.status"));
                gozo.setAberto(rs.getInt("g.aberto"));
                lista.add(gozo);
            }
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
        return lista;
    }

    public Ausencia pesquisarAusencia(Ausencia a) throws ConexaoException, ConsultaException {
        String sql = "SELECT * FROM ausencia WHERE ausenciaId =?";
        try {
            PreparedStatement pstm = g.connect().prepareStatement(sql);
            pstm.setInt(1, a.getCod());
            ResultSet rs = pstm.executeQuery();
            while (rs.next()) {
                a.setCod(rs.getInt("ausenciaId"));
                a.setGozoI(rs.getDate("gozoI"));
                a.setGozoF(rs.getDate("gozoF"));
                a.setAquisitivoInicial(rs.getDate("aquisInicial"));
                a.setAquisitivoFinal(rs.getDate("aquisFinal"));
                a.setSaldo(rs.getInt("saldo"));
                a.setObs(rs.getString("obs"));
                Servidor s = new Servidor();
                s.setMatricula(rs.getInt("matricula"));
                a.setServidor(s);
                AusenciaTipo at = new AusenciaTipo();
                at.setCodTipo(rs.getInt("idausenciaTipo"));
                a.setTipo(at);
            }
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
        return a;
    }

    public Gozo pesquisarGozo(Gozo gozo) throws ConexaoException, ConsultaException {
        String sql = "SELECT g.*,a.saldo,s.emailProf,s.nome,s.matricula "
                + "FROM gozo as g,ausencia as a,servidor as s "
                + "WHERE g.ausenciaId = a.ausenciaId "
                + "AND g.matricula = s.matricula "
                + "AND g.idGozo =?";
        try {
            PreparedStatement pstm = g.connect().prepareStatement(sql);
            pstm.setInt(1, gozo.getCod());
            ResultSet rs = pstm.executeQuery();
            while (rs.next()) {
                gozo.setCod(rs.getInt("g.idGozo"));
                gozo.setDataInicial(rs.getDate("g.dataInicial"));
                gozo.setDataFinal(rs.getDate("g.dataFinal"));
                gozo.setAberto(rs.getInt("g.aberto"));
                gozo.setStatus(rs.getInt("g.status"));
                gozo.setDias(rs.getInt("g.dias"));
                Servidor s = new Servidor();
                s.setMatricula(rs.getInt("s.matricula"));
                s.setEmailProf(rs.getString("s.emailProf"));
                s.setNome(rs.getString("s.nome"));
                gozo.setServidor(s);
                Ausencia at = new Ausencia();
                at.setCod(rs.getInt("g.ausenciaId"));
                at.setSaldo(rs.getInt("a.saldo"));
                gozo.setAusencia(at);
            }
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
        return gozo;
    }

    public void alterarAberto(Gozo gozo) throws ConexaoException, ConsultaException {
        String sql = "UPDATE gozo SET aberto=? WHERE idGozo=?";
        try {
            PreparedStatement pstm = (PreparedStatement) g.connect().prepareStatement(sql);
            pstm.setInt(1, gozo.getAberto());
            pstm.setInt(2, gozo.getCod());
            pstm.executeUpdate();
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
    }
}
